The Hidden Pitfalls of Excel Column Width

The issue started when a colleague was unable to generate column widths consistent with a client-provided template while using NPOI to export Excel files. He suggested placing the template file on the server so the program could read it and replace the values, thereby producing an Excel file that matched the client's template more closely. I rejected this. After all, while it might be acceptable for complex Word documents or Excel files containing charts, it is unnecessary for a simple table and introduces potential risks. For example, the program would become overly dependent on the Excel template file, or the file might be locked when someone downloads it, preventing others from downloading it simultaneously. Furthermore, this approach is inconsistent with our existing practices.

Later, I saw him write code similar to this:

// Affects column width
IFont font = workbook.GetFontAt(0);
font.FontName = "新細明體";
font.FontHeightInPoints = 12;

I didn't look closely at first, thinking that Excel fonts and column widths were not directly related. I questioned him again. He then tested it in front of me and found that with the same column width value, the column width generated by NPOI was indeed inconsistent with the client's template. After some research, I discovered that while I knew Excel calculates column width by dividing the width of a certain font by 256, what I didn't know was that this font is the default font set in Excel.

In other words, the font set for a specific cell has nothing to do with the column width, but the Excel default font does have an impact.

Excel Default Font

Taking Excel 2019 as an example, you can see the following in "File => Options => General":

The default font may vary across different versions of Office Excel. Since I don't have older versions of Office on hand, I couldn't test them. Although I asked ChatGPT and it replied that the default font is consistent, anyone who uses ChatGPT frequently knows it often makes things up.

TIP

The default font for NPOI 2.7.1 is Calibri, and the font size is 11.

If you change the font size to 20:

The following warning appears, requiring you to close Excel for the changes to take effect:

WARNING

After changing the default font size, you must close all Excel documents and create a new Excel document for the new settings to apply.

With the same column width value of 8.04, the column width with a font size of 20 is significantly wider than that with a font size of 12.

However, row height follows a different rule. As shown in the image below, the row height automatically increases to 28.2 as the font size changes.

If you adjust the row height to the same 16.2, the display effect will be consistent.

Setting the Default Font Using NPOI

The code example is as follows:

using IWorkbook workbook = new XSSFWorkbook();
IFont defaultFont = workbook.GetFontAt(0);
Console.WriteLine($"Default font name: {defaultFont.FontName}");
Console.WriteLine($"Default font size: {defaultFont.FontHeightInPoints}");
defaultFont.FontName = "微軟正黑體";
defaultFont.FontHeightInPoints = 20;

Console.WriteLine($"Default font name: {defaultFont.FontName}");
Console.WriteLine($"Default font size: {defaultFont.FontHeightInPoints}");

workbook.CreateSheet()
    .CreateRow(0)
    .CreateCell(0)
    .SetCellValue("Test");

using FileStream fileStream = new("Test.xlsx", FileMode.Create, FileAccess.Write);
workbook.Write(fileStream);

Console output:

Default font name: Calibri
Default font size: 11
Default font name: 微軟正黑體
Default font size: 20

The generated Excel column width is 7.84, but the column is wider than the original 8.04 width, and while the font size changed to 20, the font itself was not applied.

Setting the Default Font Using EPPlus

The code example is as follows:

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using ExcelPackage package = new();
ExcelFontXml defaultFont = package.Workbook.Styles.Fonts[0];
Console.WriteLine($"Default font name: {defaultFont.Name}");
Console.WriteLine($"Default font size: {defaultFont.Size}");
defaultFont.Name = "微軟正黑體";
defaultFont.Size = 20;
Console.WriteLine($"Default font name: {defaultFont.Name}");
Console.WriteLine($"Default font size: {defaultFont.Size}");
ExcelWorksheet sheet = package.Workbook.Worksheets
    .Add("Sheet1");
sheet.Cells[1,1,1,1].Value = "Test";

using FileStream fileStream = new("Test.xlsx", FileMode.Create, FileAccess.Write);
package.SaveAs(fileStream);

Console output:

Default font name: Calibri
Default font size: 11
Default font name: 微軟正黑體
Default font size: 20

The generated column width is 8.23, which is wider than twice the original 8.04 width, and the font size changed to 20 with the font correctly applied.

Changelog

1. 2025-08-31

   • Initial document creation.